package com.agilya.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import com.agilya.dao.daointerface.QuestionDAO;
import com.agilya.dao.exception.DAOExceptions;
import com.agilya.enumere.NiveauDifficulte;
import com.agilya.modele.Proposition;
import com.agilya.modele.Question;
import com.sun.appserv.jdbc.DataSource;

@DAOQuestion
public class JDBCQuestionDAO implements QuestionDAO {

	@Resource(name = "jdbc/QCMAgilya")
	private DataSource ds;

	@Override
	public List<Question> getAllQuestionByQCM(int idqcm) throws DAOExceptions {

		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}

		List<Question> listeQuestions = new ArrayList<>();

		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement("SELECT * FROM QUESTION q JOIN QUESTION_NN_QCM qnq ON (q.ID_QUESTION=qnq.REF_QUESTION) WHERE REF_QCM = ? ORDER BY ORDRE");) {
			psmt.setInt(1, idqcm);
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {

				int idQuestion = rs.getInt("ID_QUESTION");
				String libelle = rs.getString("LIBELLE");
				NiveauDifficulte niveauDifficulte = NiveauDifficulte.valueOf(rs.getString("NIVEAU_DIFFICULTE_QUESTION"));
				double coefficient = rs.getDouble("COEFFICIENT");
				boolean estActif = rs.getBoolean("EST_ACTIF");
				Date dateCreation = rs.getDate("DATE_CREATION");
				int version = rs.getInt("VERSION");

				Question question = new Question(idQuestion, libelle, niveauDifficulte, coefficient, estActif, dateCreation, version);
				listeQuestions.add(question);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return listeQuestions;
	}

	@Override
	public List<Proposition> getAllPropositionsActivesByQuestion(int idquestion) throws DAOExceptions {

		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}

		List<Proposition> listePropositions = new ArrayList<>();

		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement("SELECT * FROM PROPOSITION WHERE EST_ACTIVE = 1 AND REF_QUESTION = ?");) {
			psmt.setInt(1, idquestion);
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {

				int idProposition = rs.getInt("ID_PROPOSITION");
				String texte = rs.getString("TEXTE");
				boolean estCorrecte = rs.getBoolean("EST_CORRECTE");
				boolean estActive = rs.getBoolean("EST_ACTIVE");
				boolean estEliminatoire = rs.getBoolean("EST_ELIMINATOIRE");
				int coefficient = rs.getInt("COEFFICIENT");

				Proposition proposition = new Proposition(idProposition, texte, estCorrecte, estActive, estEliminatoire, coefficient);
				listePropositions.add(proposition);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return listePropositions;
	}

	@Override
	public List<Proposition> getAllPropositionsByQuestion(int idquestion) throws DAOExceptions {
		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}

		List<Proposition> listePropositions = new ArrayList<>();

		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement("SELECT * FROM PROPOSITION WHERE REF_QUESTION = ?");) {
			psmt.setInt(1, idquestion);
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {

				int idProposition = rs.getInt("ID_PROPOSITION");
				String texte = rs.getString("TEXTE");
				boolean estCorrecte = rs.getBoolean("EST_CORRECTE");
				boolean estActive = rs.getBoolean("EST_ACTIVE");
				boolean estEliminatoire = rs.getBoolean("EST_ELIMINATOIRE");
				int coefficient = rs.getInt("COEFFICIENT");

				Proposition proposition = new Proposition(idProposition, texte, estCorrecte, estActive, estEliminatoire, coefficient);
				listePropositions.add(proposition);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return listePropositions;
	}

	@Override
	public List<Proposition> getAllBonnesPropositionsByQuestion(int idquestion) throws DAOExceptions {
		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}

		List<Proposition> listeBonnesPropositions = new ArrayList<>();

		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement("SELECT * FROM PROPOSITION WHERE REF_QUESTION = ? AND EST_CORRECTE=1");) {
			psmt.setInt(1, idquestion);
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {

				int idProposition = rs.getInt("ID_PROPOSITION");
				String texte = rs.getString("TEXTE");
				boolean estCorrecte = rs.getBoolean("EST_CORRECTE");
				boolean estActive = rs.getBoolean("EST_ACTIVE");
				boolean estEliminatoire = rs.getBoolean("EST_ELIMINATOIRE");
				int coefficient = rs.getInt("COEFFICIENT");

				Proposition proposition = new Proposition(idProposition, texte, estCorrecte, estActive, estEliminatoire, coefficient);
				listeBonnesPropositions.add(proposition);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return listeBonnesPropositions;
	}

	@Override
	public Proposition getPropositionById(int idproposition) throws DAOExceptions {

		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}

		Proposition proposition = null;

		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement("SELECT * FROM PROPOSITION WHERE ID_PROPOSITION = ?");) {
			psmt.setInt(1, idproposition);
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {

				String texte = rs.getString("TEXTE");
				boolean estCorrecte = rs.getBoolean("EST_CORRECTE");
				boolean estActive = rs.getBoolean("EST_ACTIVE");
				boolean estEliminatoire = rs.getBoolean("EST_ELIMINATOIRE");
				int coefficient = rs.getInt("COEFFICIENT");

				proposition = new Proposition(idproposition, texte, estCorrecte, estActive, estEliminatoire, coefficient);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return proposition;
	}

	@Override
	public Question getQuestionById(int idquestion) throws DAOExceptions {
		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}
		Question question = null;
		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement("SELECT * FROM QUESTION WHERE ID_QUESTION = ?");) {
			psmt.setInt(1, idquestion);
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {

				idquestion = rs.getInt("ID_QUESTION");
				String libelle = rs.getString("LIBELLE");
				NiveauDifficulte niveauDifficulte = NiveauDifficulte.valueOf(rs.getString("NIVEAU_DIFFICULTE_QUESTION"));
				double coefficient = rs.getDouble("COEFFICIENT");
				boolean estActif = rs.getBoolean("EST_ACTIF");
				Date dateCreation = rs.getDate("DATE_CREATION");
				int version = rs.getInt("VERSION");

				question = new Question(idquestion, libelle, niveauDifficulte, coefficient, estActif, dateCreation, version);

			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}
		return question;
	}

	@Override
	public List<Question> getAllQuestionsEtPropositionsByQCM(int idqcm) throws DAOExceptions {

		List<Question> listeQuestionsEtPropostion = getAllQuestionByQCM(idqcm);

		for (Question question : listeQuestionsEtPropostion) {
			question.setListePropositions(getAllPropositionsActivesByQuestion(question.getId()));
		}

		return listeQuestionsEtPropostion;
	}

	@Override
	public Integer enregistrer(Question q, int idChapitre) throws DAOExceptions {
		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}
		Connection c;
		Integer idquestion = null;
		try {
			c = ds.getConnection();
			PreparedStatement psmt = c.prepareStatement("INSERT INTO `QCMAgilya`.`QUESTION` (`LIBELLE`, `NIVEAU_DIFFICULTE_QUESTION`, `COEFFICIENT`, `EST_ACTIF`, `DATE_CREATION`, `VERSION`, `REF_CHAPITRE`, `REF_SACHANT`) VALUES(?, ?, ?, ?, ?, ?, ?, ?);", java.sql.Statement.RETURN_GENERATED_KEYS);
			// Statement.RETURN_GENERATED_KEYS = permet de récupéré l'id généré
			psmt.setString(1, q.getLibelle());
			psmt.setString(2, q.getNiveauDifficulte().name());
			psmt.setDouble(3, q.getCoefficient());
			psmt.setBoolean(4, true); // état de la question actif par défaut.
			psmt.setTimestamp(5, (Timestamp) q.getDateCreation());
			psmt.setInt(6, 0);// version - on affecte 0 au numéro de version tant que le minimum de proposition n'est pas inséré
			psmt.setInt(7, idChapitre);// chapitre
			psmt.setInt(8, 1);// sachant - A MODIFIER PLUS TARD
			psmt.executeUpdate();

			// récupération de l'id de la question auto généré en base - utile pour les propositions associées
			ResultSet rs = psmt.getGeneratedKeys();

			if (rs.next()) {
				idquestion = (int) rs.getLong(1);
			}
			System.out.println("JDBCQuestionDAO, enregistrer question renvoie:  idquestion =" + idquestion);

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}
		return idquestion;
	}

	@Override
	public List<Question> getAllQuestionByChapitre(int idChapitre) throws DAOExceptions {
		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}
		String requete = "SELECT * FROM QUESTION WHERE REF_CHAPITRE = ?";
		List<Question> listeQuestions = new ArrayList<>();
		try (Connection c = ds.getConnection(); PreparedStatement psmt = c.prepareStatement(requete);) {
			psmt.setInt(1, idChapitre);
			ResultSet rs = psmt.executeQuery();

			while (rs.next()) {

				int idQuestion = rs.getInt("ID_QUESTION");
				String libelle = rs.getString("LIBELLE");
				NiveauDifficulte niveauDifficulte = NiveauDifficulte.valueOf(rs.getString("NIVEAU_DIFFICULTE_QUESTION"));
				double coefficient = rs.getDouble("COEFFICIENT");
				boolean estActif = rs.getBoolean("EST_ACTIF");
				Date dateCreation = rs.getDate("DATE_CREATION");
				int version = rs.getInt("VERSION");

				Question question = new Question(idQuestion, libelle, niveauDifficulte, coefficient, estActif, dateCreation, version);
				listeQuestions.add(question);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return listeQuestions;
	}

	@Override
	public List<Question> getAllQuestionEtPropositionsByChapitre(int idChapitre) throws DAOExceptions {

		List<Question> listeQuestionsEtProposition = getAllQuestionByChapitre(idChapitre);

		for (Question question : listeQuestionsEtProposition) {
			question.setListePropositions(getAllPropositionsActivesByQuestion(question.getId()));
		}

		return listeQuestionsEtProposition;

	}

	@Override
	public Integer enregistrer(Proposition proposition, int idquestion) throws DAOExceptions {
		if (ds == null) {
			throw new DAOExceptions("Base de donnée non accessible. Data Source non initialisé");
		}
		Connection c;
		Integer idproposition = null;
		try {
			c = ds.getConnection();
			PreparedStatement psmt = c.prepareStatement("INSERT INTO `QCMAgilya`.`PROPOSITION` (`TEXTE`, `EST_CORRECTE`, `EST_ACTIVE`, `EST_ELIMINATOIRE`, `COEFFICIENT`, `REF_QUESTION`, `REF_SACHANT`) VALUES (?, ?, ?, ?, ?, ?, ?);");
			psmt.setString(1, proposition.getTexte());
			psmt.setBoolean(2, proposition.estCorrecte());
			psmt.setBoolean(3, proposition.estActive());
			psmt.setBoolean(4, proposition.estEliminatoire());
			psmt.setDouble(5, proposition.getCoefficient());
			psmt.setInt(6, idquestion);
			psmt.setInt(7, 1); // sachant - à modifier plus tard - doit être l'auteur de la proposition
			psmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOExceptions(e.getMessage(), e);
		}

		return idproposition;
	}
}
